const express = require('express');
const router = express.Router();
const con = require('../db/mysql');
router.get('/', async (req, res) => {
    const { page, limit, starttime, endtime, types, state, pay, status, contract, company } = req.query;
    try {
        let where = "";
        if (starttime) {
            where += ` AND contract.starttime >= '${starttime}' AND contract.endtime <= '${endtime}'`
        }
        if (types !== undefined) {
            where += ` AND contract.types = ${types}`
        }
        if (state !== undefined) {
            where += ` AND contract.state = ${state}`
        }
        if (pay !== undefined) {
            where += ` AND contract.pay = ${pay}`
        }
        if (status !== undefined) {
            where += ` AND contract.status = ${status}`
        }
        if (company) {
            where += ` AND customer.company LIKE '%${company}%' `
        }
        if (contract) {
            where += ` AND contract.company LIKE '%${contract}%' `
        }
        const result = await con.query(`SELECT contract.*,customer.company,customer.industry FROM  contract,customer WHERE contract.cid=customer.id ${where} ORDER BY contract.id DESC LIMIT ${limit} OFFSET ${page * limit - limit};SELECT COUNT(*) AS total FROM contract WHERE 1 ${where}`);
        if (result.length) {
            res.json({
                msg: "查询成功",
                code: 200,
                data: result[0],
                total: result[1][0].total
            });
        }
    } catch (error) {
        throw error;
    }
})
router.post('/', async (req, res) => {
    const { code, undertake, cid, handler, types, starttime, endtime, smethods, phone, pay, paytime, invoice, paperfile, contract, sign, signtime, state, deadline, con_name, money, already, situation, digital, agreement,upload } = req.body;
    try {
        const result = await con.query(`INSERT INTO contract (code,undertake ,cid ,handler ,types ,starttime ,endtime ,smethods ,phone ,pay ,paytime ,invoice ,paperfile ,contract ,sign ,signtime ,state ,deadline ,con_name ,money , already ,situation ,digital ,agreement,upload ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`, [code, undertake, cid, handler, types, starttime, endtime, smethods, phone, pay, paytime, invoice, paperfile, contract, sign, signtime, state, deadline, con_name, money, already, situation, digital, agreement,upload]);
        if (result.affectedRows === 1) {
            res.json({
                msg: "新增成功",
                code: 200,
            })
        } else {
            res.json({
                msg: "新增失败",
                code: 500,
            })
        }
    } catch (error) {
        throw error;
    }
})
router.delete('/:id', async (req, res) => {
    const { id } = req.params;
    try {
        const result = await con.query(`DELETE FROM contract WHERE id=?`, id);
        if (result.affectedRows === 1) {
            res.json({
                msg: "删除成功",
                code: 200,
            })
        } else {
            res.json({
                msg: "删除失败",
                code: 500,
            })
        }
    } catch (error) {
        throw error;
    }
})
router.get('/:id', async (req, res) => {
    const { id } = req.params;
    try {
        const result = await con.query(`SELECT * FROM contract WHERE id=?`, id);
        if (result.length) {
            res.json({
                msg: "查询成功",
                code: 200,
                data: result[0]
            })
        } else {
            res.json({
                msg: "查询失败",
                code: 500,
            })
        }
    } catch (e) {
        throw e;
    }
})
router.put('/:id', async (req, res) => {
    const { id } = req.params;
    const { code, undertake, cid, handler, types, starttime, endtime, smethods, phone, pay, paytime, invoice, paperfile, contract, sign, signtime, state, deadline, con_name, money, already, situation, digital, agreement, status,upload } = req.body;
    try {
        const result = await con.query(`UPDATE contract SET code=?,undertake=? ,cid=? ,handler=? ,types=? ,starttime=? ,endtime=? ,smethods=? ,phone=? ,pay=? ,paytime=? ,invoice=? ,paperfile=? ,contract=? ,sign=? ,signtime=? ,state=? ,deadline=? ,con_name=? ,money=? , already=? ,situation=? ,digital=? ,agreement=? ,status=?,upload=? WHERE id=?`, [code, undertake, cid, handler, types, starttime, endtime, smethods, phone, pay, paytime, invoice, paperfile, contract, sign, signtime, state, deadline, con_name, money, already, situation, digital, agreement, status,upload, id]);
        if (result.changedRows === 1) {
            res.json({
                msg: "修改成功",
                code: 200,
            })
        } else {
            res.json({
                msg: "未修改任何数据或修改失败",
                code: 500,
            })
        }
    } catch (error) {
        throw error;
    }
})


module.exports = router;